<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



$link = mysql_connect("localhost", 'root', 'ornitorrinco666') or die(mysql_error());
mysql_select_db("permutas", $link) or die(mysql_error());

$fp = fopen("permutas.csv", "r");
while (( $data = fgetcsv($fp, 20000, ",", "\"")) !== false) {

    $codigo = $data[0];
    $solicitud = $data[2];
    $pdr_departamento = utf8_decode($data[8]);
    $pdr_municipio = utf8_decode($data[9]);
    $pdr_nombre = $data[11];
    $pdr_matricula = $data[12];
    $pdr_catastral = $data[13];
    $pdr_area = $data[40];

    $sql = "INSERT INTO proyects(codigo,numeroSolicitud,call_id) VALUES ('$codigo','$solicitud',1)";
    mysql_query($sql, $link) or die(mysql_error());
    $proyect_id = mysql_insert_id($link);

    $sql = "SELECT a.id FROM cities a LEFT JOIN departaments b on a.departament_id=b.id WHERE a.nombre='$pdr_municipio' AND b.nombre='$pdr_departamento'";

    $res = mysql_query($sql, $link) or die(mysql_error());
    $city_id = 0;
    while ($row = mysql_fetch_array($res)) {
        $city_id = $row['id'];
    }
    if ($city_id == 0) {
        echo "$codigo " . $pdr_municipio . " " . $pdr_departamento . "<br>";
    }

    $sql2 = "INSERT INTO properties (nombre,matricula,cedulaCatastral,area,proyect_id,city_id) VALUES('$pdr_nombre','$pdr_matricula','$pdr_catastral','$pdr_area','$proyect_id','$city_id')";
    $res = mysql_query($sql2, $link) or die(mysql_error());
    $property_id = mysql_insert_id($link);

    
    //INSERTAR EVALUACIONES DE PREDIOS
    
    
     $sql5 = "INSERT INTO property_evaluations (property_id) values($property_id)";
    $res = mysql_query($sql5, $link) or die(mysql_error());
    $property_evaluation_id = mysql_insert_id($link);

    //CREACION DE REQUISITOS DE aspirantes
    $sql6 = "SELECT id FROM `requirements` WHERE `tipo` = 'Predio' ORDER BY id ASC";
    $requisitos = mysql_query($sql6, $link) or die(mysql_error());
    $conteo =23 ;
    while ($row1 = mysql_fetch_array($requisitos)) {
        $req_id = $row1['id'];

        $cal = $data[$conteo];
        $sql="INSERT INTO property_requirements (property_evaluation_id,requirement_id,calificacion) VALUES ('$property_evaluation_id','$req_id','$cal')";
        mysql_query($sql, $link) or die(mysql_error());

       $conteo++;
       //echo $sql."<br>"; 
    }
    
    
    
    //INSERTAR PROPIETARIOS

   
    $cedula = $data[3];
    $nombre_prop = $data[4];
    $apellido_prop = $data[5];
    $apellidos = explode(" ", $apellido_prop);
    $primer = $apellidos[0];
    $segundo = $apellidos[1];

    $dep_prop = $data[6];
    $city_prop = $data[7];
    $sql3 = "SELECT a.id FROM cities a LEFT JOIN departaments b on a.departament_id=b.id WHERE a.nombre='$city_prop' AND b.nombre='$dep_prop'";
    $res = mysql_query($sql3, $link) or die(mysql_error());
    $prop_city_id = 0;
    while ($row = mysql_fetch_array($res)) {
        $prop_city_id = $row['id'];
    }

    if ($prop_city_id == 0) {
        echo "$codigo " . $pdr_municipio . " " . $pdr_departamento . "<br>";
    }

    $sql4 = "INSERT INTO owners (nombres, primer_apellido, segundo_apellido, documento, property_id, city_id)VALUES  ('$nombre_prop','$primer','$segundo','$cedula','$property_id','$prop_city_id')";

    //echo $sql4."<br>";
    $owner_id = 0;
    mysql_query($sql4, $link) or die(mysql_error());
    $owner_id = mysql_insert_id($link);

    $sql5 = "INSERT INTO owner_evaluations (owner_id) values($owner_id)";
    $res = mysql_query($sql5, $link) or die(mysql_error());
    $owner_evaluation_id = mysql_insert_id($link);

    //CREACION DE REQUISITOS DE aspirantes
    $sql6 = "SELECT id FROM `requirements` WHERE `tipo` = 'Propietario' ORDER BY id ASC";
    $requisitos = mysql_query($sql6, $link) or die(mysql_error());
    $cont = 14;
    while ($row1 = mysql_fetch_array($requisitos)) {
        $req_id = $row1['id'];

        $cal = $data[$cont];
        $sql="INSERT INTO owner_requirements (owner_evaluation_id,requirement_id,calificacion) VALUES ('$owner_evaluation_id','$req_id','$cal')";
        mysql_query($sql, $link) or die(mysql_error());

       $cont++;
       //echo $sql."<br>"; 
    }
}
?>
